Copyright: Copyright 1995, by Spreadsheet Solutions


Contents
Spreadsheet Solutions

Custom Add-In Functions and

Utilities for Excel 5.x

Last Revision: 2/20/95

All Custom Add-Ins from Spreadsheet Solutions can be found in the MS Excel Forum on CompuServe.  Search for Contributor 74002,2373.  You may also use this address for E-mail (or, on the Internet: 74002.2373@compuserve.com).

Installation

Custom Function/Utilities Topics

HOLIDAYS!

PlayWAV

WhichDOW

TalkBack

Registration Form

DISCLAIMER OF WARRANTY

This software is distributed "as is", without any warranty as to performance or any other warranties whether expressed or implied.  Because of the many hardware and software environments into which this program may be used, no warranty of fitness for a particular purpose is offered. The user must assume the entire risk of using the program.  Any liability of the seller will be limited exclusively to product replacement or the refund of the registration fee.

Excel is a Copyright of Microsoft Inc.

Copyright 1995, by Spreadsheet Solutions


HOLIDAYS!

Welcome to HOLIDAYS!© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

HOLIDAYS! is Shareware.  If you continue to use HOLIDAYS! after a 30 day evaluation period, you must register it, or remove it from your computer.

FunctionWizard Category= HOLIDAYS!

HOLIDAYS! Syntax:

=HolidayFunction(year)

HolidayFunction	The name of the Holiday, e.g. MothersDay().

year	Any valid year (*Religious Holidays: 1990-2020, or #N/A)

When a function returns a date serial number, you must format the cell to display a date!

I'll use Bastille Day to make an example of the HOLIDAYS! functions.

Function Example	Result

=BastilleDay(1994)	7/14/94 (Date serial number)

=BastilleDay(A1)	7/14/95*

*where cell A1 contains the VALUE 1995.

=BastilleDay(NOW())	7/14/94*

*where NOW() evaluates to a date in the year 1994.  (your computer clock is sometime in 1994)

=Bastille.Day(YEAR(C3))	7/14/95*

*where cell C3 contains a date serial number which evaluates to a date in the year 1995.

=BastilleDay(YEAR(C3))	Bastille Day - 7/14/95*

*where cell C3 contains a date serial number which evaluates to a date in the year 1995.

IsHoliday() Function

***** Registered Version Only *****

The =IsHoliday() Function determines whether a date (serial_number) falls on any one of the HOLIDAYS! Functions.  The function passes the year of serial_number, to each of the HOLIDAYS! functions, and compares the result of the holiday dates against serial_number.

*** IMPORTANT ***

IsHoliday() Syntax:

=IsHoliday(serial_number,showtext,showdate)

serial_number	Any date serial number for the years 1900-2??? inclusive*

(Religious Holidays: 1990-2020, or #N/A),

or a formula/reference to a cell, resulting in a date serial number.

showtext	0, 1, or omitted - INTEGER

TRUE if serial_number falls on any HOLIDAYS! Function.

FALSE if no HOLIDAYS!.

2, - VARIANT

TEXT (date/name of Holiday), if serial_number falls on any HOLIDAYS!.

Date Serial Number, if no HOLIDAYS!.

Examples

1/1/1995	=IsHoliday($B1,1,TRUE)	=IsHoliday($B1,0,TRUE)	=IsHoliday($B1,0,TRUE)*1

=+A1+1

1/1/1995	New Years Day: 1/1/95	TRUE	1

2-Jan-95	FALSE	FALSE	0

3-Jan-95	FALSE	FALSE	0

4-Jan-95	FALSE	FALSE	0

5-Jan-95	FALSE	FALSE	0

6-Jan-95	Epiphany: 1/6/95	TRUE	1

7-Jan-95	FALSE	FALSE	0

8-Jan-95	FALSE	FALSE	0

1/1/1995	=IsHoliday($B1,1,FALSE)	=IsHoliday($B1,0,FALSE)	=IsHoliday($B1,0,FALSE)*1

=+A1+1

1/1/1995	New Years Day	TRUE	1

2-Jan-95	FALSE	FALSE	0

3-Jan-95	FALSE	FALSE	0

4-Jan-95	FALSE	FALSE	0

5-Jan-95	FALSE	FALSE	0

6-Jan-95	Epiphany	TRUE	1

7-Jan-95	FALSE	FALSE	0

8-Jan-95	FALSE	FALSE	0

IsHoliday() Function	Result

=IsHoliday(DATE(94,12,25))	TRUE  (Logical)

=IsHoliday(DATE(94,12,25),1)	TRUE  (Logical)

=IsHoliday(DATE(94,12,25),2)	12/25/94 - Christmas Day (Text)

=IsHoliday(DATE(94,12,24))	FALSE

=IsHoliday(DATE(94,12,24),1)	FALSE

=IsHoliday(DATE(94,12,24),2)	12/24/94

=IsHoliday(A1,1)	FALSE*

*where cell A1 contains the date 12/24/94.

=IsHoliday(NOW(),2)	12/25/94 - Christmas Day*

*where NOW() evaluates to 12/25/94.

=IsHoliday(C3+5)	TRUE*

*where cell C3 contains the date 6/30/94.  [6/30/94+5 evaluates to 7/4/94]

=IsHoliday(NOW()+5,2)	7/4/94 - Independence Day*

*where NOW() evaluates to 6/30/94.  [6/30/94+5 evaluates to 7/4/94]

Copyright 1995, by Spreadsheet Solutions


PlayWAV

Welcome to PlayWAV© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

PlayWAV is Shareware.  If you continue to use PlayWAV after a 30 day evaluation period, you must register it, or remove it from your computer.

FunctionWizard Category= PlayWAV

PlayWAV Example

Syntax:

=PlayWAV("Filename")

Filename	Any valid WAV filename on your system (including the extension).

If the file IS NOT in your \WINDOWS directory, you MUST

include the Full Path.

Calling PlayWAV from your VBA Code:

In order to use PlayWAV in your VBA Code, you must also specify it as a Reference.

Not too tough to do.

   Open PLAYWAV.XLA if it's not already open.

   Select your Module sheet where you want to add PlayWAV().

   Select Tools|References... from the menu, and then select PLAYWAV.XLA

   Select OK

You're ready to go.

See Also: WAVDEMO.XLS which should be included in your PlayWAV archive.

Copyright 1995, by Spreadsheet Solutions


WhichDOW

Welcome to WhichDOW© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

WhichDOW is Shareware.  If you continue to use WhichDOW after a 30 day evaluation period, you must register it, or remove it from your computer.

FunctionWizard Category= WhichDOW

Do your Excel Models depend on Dates, and looking up the first Monday of every month is getting tiresome? WHICHDOW functions make it easy!  All the functions are the combinations of 12 words, First, Second, Third, Fourth, Last, and Sun, Mon, Tue, Wed, Thu, Fri, and Sat.  Just put when before the day, and a period in between! e.g. ThirdSun(94,1) will return the Third Sunday in January 1994.

Syntax:

=LastFri(year,month)

year	Any valid year, or formula that results in a valid year.

month	Any valid month, or formula that results in a valid month.

Calling WhichDOW from your VBA Code:

In order to use WhichDOW in your VBA Code, you must also specify it as a Reference.

Not too tough to do.

   Open WHICHDOW .XLA if it's not already open.

   Select your Module sheet where you want to add a WhichDOW function.

   Select Tools|References... from the menu, and then select WHICHDOW .XLA

   Select OK

You're ready to go.

See Also: DOWDEMO.XLS which should be included in your WhichDOW archive.

WHICHDOW Examples:

=ThirdThu(94,3)	Result: Third Thu in March 1994.

=SecondWed(94)	Result: Second Wed of the CURRENT MONTH, 1994.

=SecondWed(94,)	Result: Same as above.

=LastFri(,8)	Result: Last Fri in August of the CURRENT YEAR.

(comma before month required!)

=FourthSun()	Result: Fourth Sun of CURRENT MONTH and YEAR.

Where the cell A1 contains 94 (year), and B1 contains 4 (month-April), then:

=FirstMon(A1,B1) 	Result: First Mon in April 1994.

Or, you may embed DATE type functions as arguments like: =Last.Mon(YEAR(NOW()),5).

WHICHDOW Function Listing

NOTE: Depending on the month/year, Fourth???() and Last???() may return the SAME date.

=FirstSun(year,month)

=FirstMon(year,month)

=FirstTue(year,month)

=FirstWed(year,month)

=FirstThu(year,month)

=FirstFri(year,month)

=FirstSat(year,month)

=SecondSun(year,month)

=SecondMon(year,month)

=SecondTue(year,month)

=SecondWed(year,month)

=SecondThu(year,month)

=SecondFri(year,month)

=SecondSat(year,month)

=ThirdSun(year,month)

=ThirdMon(year,month)

=ThirdTue(year,month)

=ThirdWed(year,month)

=ThirdThu(year,month)

=ThirdFri(year,month)

=ThirdSat(year,month)

=FourthSun(year,month)

=FourthMon(year,month)

=FourthTue(year,month)

=FourthWed(year,month)

=FourthThu(year,month)

=FourthFri(year,month)

=FourthSat(year,month)

=LastSun(year,month)

=LastMon(year,month)

=LastTue(year,month)

=LastWed(year,month)

=LastThu(year,month)

=LastFri(year,month)

=LastSat(year,month)

Copyright 1995, by Spreadsheet Solutions


PERIODIC

Welcome to PERIODIC© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

PERIODIC is Shareware.  If you continue to use PERIODIC after a 30 day evaluation period, you must register it, or remove it from your computer.

FunctionWizard Category= User Defined

Syntax:

All but one function have the same syntax.

=AtomicNumber(symbol) is a function which returns the atomic_number (required for the remaining functions) when you provide the element symbol, as text. This is useful when you know the element's symbol, but not it's number.

symbol	any atomic symbol (H, He, Li, Be, B, C, N, O, etc.) as text.

e.g. =AtomicNumber("He") returns the value 2.

The Remaining Functions:

atomic_number	any atomic number, a value from 1 - 110.

display_units	FALSE or omitted* - returns the function's value.

(*If omitted, you should omit format_text as well).

TRUE** - returns the value as TEXT, with units as suffix.

(**When TRUE, you MUST specify the format_text).

format_text	is a number format from the Number dialog box in text form.

(see the Excel Documentation for FORMAT)

When display_units is TRUE, you may NOT use the result in a formula (it's TEXT!).

e.g.  =AtomicWeight(3,TRUE,"0.0000") [Lithium] returns TEXT as: 13.1000 cm3/mol,

while =AtomicWeight(3) returns 13.1 as a NUMBER (format depends on YOUR cell format).

PERIODIC! FUNCTIONS:	UNITS:

=_1st.IonPotential(atomic_number,display_units,format_text)	V

=_2ndIonPotential(atomic_number,display_units,format_text)	V

=_3rdIonPotential(atomic_number,display_units,format_text)	V

=AtomicGroup(atomic_number)	NA

=AtomicNumber(symbol)	NA

=AtomicRadius(atomic_number,display_units,format_text)	A

=AtomicSymbol(atomic_number)	NA

=AtomicVolume(atomic_number,display_units,format_text)	cm3/mol

=AtomicWeight(atomic_number,display_units,format_text)	based on carbon-12

=BoilingPoint(atomic_number,display_units,format_text)	Deg C

=CovalentRadius(atomic_number,display_units,format_text)	A

=CrystalStructure(atomic_number)	NA

=Density(atomic_number,display_units,format_text)	g/cm3

=DiscoveryDate(atomic_number)	NA

=ElectricalConductivity(atomic_number,display_units,format_text)	10^6/cm ohm

=Electronegativity(atomic_number)	NA

=ElementName(atomic_number)	NA

=FillingOrbital(atomic_number)	NA

=HeatOfFusion(atomic_number,display_units,format_text)	kJ/mol

=HeatVaporization(atomic_number,display_units,format_text)	kJ/mol

=MeltingPoint(atomic_number,display_units,format_text)	Deg. C

=OxidationState(atomic_number)	NA

=Shells(atomic_number)	NA

=SpecificHeat(atomic_number,display_units,format_text)	J/gK

=ThermalConductivity(atomic_number,display_units,format_text)	W/cmK

When a Function returns #VALUE!

There are many combinations of function and element whose result is either unknown, or doesn't apply.  An obvious example of this would be the function  =ElectricalConductivity(1).  Element 1 (Hydrogen) has a conductivity which is indeterminate, and returns #VALUE!, while =Electrical.Conductivity(26) (Lead) returns 0.0993.

If one of my functions returns a #VALUE! error, and you can document a value that's been determined for the element, please feel free to let me know, as I'm always interested in keeping my programs up to date.

PERIODIC! CONSTANTS:	UNITS:

=Avagadros.Number()	mol/mole

=SpeedOfLight()	m/sec

=ElectronRestMass()	kg

=ProtonRestMass()	kg

=NeutronRestMass()	kg

=PlancksConstant()	J*s

=BoltzmannsConstant()	J/K

=GravitationalAcceleration()	m/sec^2

=GravitationalConstant()	N*m^2/kg^2

=FaradayConstant()	coul/eq

Copyright 1995, by Spreadsheet Solutions


Installation

INSTALLING Custom Functions

If you received or downloaded an archive, copy the .ZIP to this subdirectory, and "unzip" it there.

I recommend a subdirectory "under" your Excel directory.  Normally, during its install, Excel will create directories something like ... C:\EXCEL\LIBRARY\CROSSTAB, as well as others.  I recommend creating a subdirectory named C:\EXCEL\LIBRARY\SSADDINS, and placing ALL the files there.

IMPORTANT -  In order to use the online help, you must copy, or move the file SSADDINS.HLP to your Excel Directory.

USING Custom Functions

You can use the custom functions by simply Opening the .XLA file from within Excel, or, double click on it's filename in File Manager to load it, and Excel.

Registered users will Avoid the Opening Shareware Dialog Box.

To enter a Custom Function to your worksheet, select [Insert/Function...] from the Excel menu, and then select [HOLIDAYS!], or [User Defined], etc. as the [Function Category:] for a list of the Custom Functions (in the [Paste Function] list).

Select a Custom function, and select Next.  Enter your arguments, or references, and press select Finish.  Change the Number Format of the cell if necessary.  If you know the syntax of the function, you can more easily just type the function, like any other function, =SUM(), =GetINIString(), =PlayWAV(), or =ArborDay().

HINTS:

Copy any ???????.XLA to your XLSTART subdirectory to load it every time you use Excel.

e.g. C:\EXCEL\XLSTART\HOLIDAYS.XLA

If you open a document containing a Custom Function, and the corresponding .XLA is NOT OPEN, a message indicating that the function is "too complex" will appear.  To propery recalculate the functions, you MUST open the corresponding .XLA.

Copyright 1995, by Spreadsheet Solutions


PlayWAV Example
PlayWAV Examples

The following examples play the TADA.WAV file in your \WINDOWS directory.

x returns 1 if the file was played, and 0 if not.

Sub TestWAV()

Dim x As Integer

    'play the file

    x = PlayWAV("tada.wav")

End Sub

Sub TestWAV()

Dim Fname As String

Dim x As Integer

    'somewhere along the lines FName gets a filename...

    FName = "C:\WAVS\TADA.WAV"

    'play the file

    x = PlayWAV(Fname)

End Sub

Copyright 1995, by Spreadsheet Solutions


TalkBack

Welcome to TalkBack© - The WAV Number Proofreader for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

TalkBack is Shareware.  If you continue to use TalkBack after a 30 day evaluation period, you must register it, or remove it from your computer.

NOTE: A sound card is NOT REQUIRED, (but recommended) since TalkBack can use the PC Speaker Driver (available from Microsoft, or from many BBS's).  However, you will need one or the other.

Proofread your worksheets with a new friend.  With TalkBack you no longer need a co-worker to read off a list of numbers for you to proof them. TalkBack does it all for you!  Well, maybe I went too far with that last statement, however I do think you'll find it an invaluable tool.  While there certainly are more sophisticated programs that will read numbers (and text) to you in "correct" english, oftentimes they're complicated, very expensive, and not directly integrated with Excel, making them difficult to use.  TalkBack is none of these!  Directly integrated with Excel (written in VBA, and accessible from the "Tools" menu), TalkBack is both inexpensive, and easy to use.  Simply select the range you want to proof, start TalkBack, and you're on your way!

Using TalkBack

Simply select the range you want read back to you, and then select either Default TalkBack, or TalkBack... from the Excel "Tools" menu.  These menu items are only available in the "Worksheet" Tools menu, as TalkBack will only work when the active sheet is a Worksheet (as opposed to a Chart, Dialog, or Macro sheet).  A shortcut key combination has also been assigned so you can start Default TalkBack with SHIFT+CTRL+T instead of using the menu.

If you select Default TalkBack, (or SHIFT+CTRL+T) TalkBack will begin speaking immediately, using the defaults as described next.

If you select TalkBack..., TalkBack will display a dialog with three default options. These options will be saved to your EXCEL5.INI file so they can be "remembered" between Excel sessions making it unnecessary reset them every time you use Excel.  If you uninstall TalkBack, you should remove these entries from your INI file which is located in you WINDOWS directory.

Say Columns/Rows (check box)

Enable this box to have TalkBack say the Column/Row numbers of the data whenever it

starts a new column.

Error if Text (check box)

Enable this box to have TalkBack say "Invalid Entry", if the cell contains text.

Error if Blank (check box)

Enable this box to have TalkBack say "Uh Oh, if the cell is blank.

Delay (msecs) (edit box)

Enter the number of milliseconds TalkBack should wait between speaking the numbers

in each cell of the selected range.  The default is 1000 (0.5 seconds).

When you're set, simply select the Start Talking button, and TalkBack will take it from there, reading back to you all the numbers in the selected range.

Simply press ESC at any time if you wish to Pause or End TalkBack before it's finished.

How TalkBack Speaks/Limitations

As I mentioned earlier, TalkBack is not as sophisticated as some other commercial products (but then it doesn't carry their pricetag either).  And there's a reason for this.  MEMORY!  I wanted something small and easily accessible that wouldn't drain my system memory (many commercial products have big DLL's which use large amounts of memory).  Many of the commercial products can also use "correct" english when reading numbers (and/or text) from an application, and truely "analyzes" the data it receives before speaking it.

For instance, a commercial product might read 435.1 as "Four Hundred Thirty Five Point One".  TalkBack on the other hand, will read this same number as "Four Three Five Point One".

In addition, TalkBack will only read numbers, and does not support reading text as some commercial products do.  When TalkBack comes to a blank cell, or one with text, it can say "Uh Oh", "Invalid Entry", or simply skip it and proceed to the next cell.

Speaking of proceeding to the next cell, the Shareware version of TalkBack reads all the numbers in the first column, before it moves to the second column (analogous to "Down and then Over" when Printing multiple pages).  Registered Users will also have the option "Across and then Down" (reading all the numbers in the first row, before it moves to the second row).

Installing TalkBack

TalkBack requires two files:

TALKBACK.XLA

TALKBACK.WAV

TALKBACK.WAV MUST be placed in your main EXCEL directory, (e.g. c:\excel\talkback.wav, or c:\msoffice\excel5\talkback.wav) however, the TALKBACK.XLA add-in can be placed in any directory you wish.

If the WAV file is not found in your EXCEL directory when you load TalkBack, it will display a message to that effect (indicating where it should be), and will not allow you to proceed using TalkBack.  You must Exit Excel, move the file to the directory specified in the message, and then restart Excel in order to use TalkBack.

Loading the TalkBack Add-In

In order to use TalkBack it must be loaded.  You can do this in different ways depending on how often you use it.

To make it always available...

Specify it as an Add-In (Tools|Add-Ins... then "Browse" for TALKBACK.XLA)

OR

Place TALKBACK.XLA in your XLSTART directory.

To use it for a single session...

Simply open it as you would any other .XL? file.

TalkBack Credits/Acknowledgments

Credit to: Richard Mansfield, Evangelos Petroutsos, and Ventana Press for their book:

Visual Basic Power Toolkit from which I gleened all that was necessary to create this add-in.

Copyright 1995, by Spreadsheet Solutions


Registration
Registration Form

Your registrations allow me to improve the Add-Ins, and write other programs for you to enjoy, at a reasonable cost.  Registration has many other benefits, including: technical support via mail and/or Compuserve, reduced prices on other Shareware from Spreadsheet Solutions, and more!  With registration you'll receive a disk with the smaller registered version of the Add-In (no nag screens), along with shareware versions of all my other Excel Add-ins to try risk free, containing over 170 custom functions in all!

To register an Add-In, select the Print button above, to print this topic. Enter your information, and send it to (payable to Spreadsheet Solutions in US$):

P. Scott Antony

c/o Spreadsheet Solutions

P.O. Box 11047

Shorewood WI,  53211

Registration Information:

First Name:	MI:	Last Name:

|__|__|__|__|__|__|__|__|__|__|__|__|	|__|	|__|__|__|__|__|__|__|__|__|__|__|__|__|__|

Company:

|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|

Address:

|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|

City:

|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|

State:|__|__|    Zip:|__|__|__|__|__|-|__|__|__|__|    CIS E-Mail #|__|__|__|__|__|,|__|__|__|__|

Other Internet Address ____________________________________________

____ Please send me information about a multiple site licence for _______________________.

Shareware Demo Disk Only	_____  x  $5.00 =	$___________

Registered Version(s) of HOLIDAYS! 	_____	x$15.00 =	$___________

Registered Version(s) of PlayWAV 	_____	x  $8.00 =	$___________

Registered Version(s) of PERIODIC 	_____	x$15.00 =	$___________

Registered Version(s) of WhichDOW 	_____	x$15.00 =	$___________

Registered Version(s) of TalkBack 	_____	x$15.00 =	$___________

Registered Version(s) of INISYSDB 	_____	x$15.00 =	$___________

For the following options enter checks above to make your selections.

Any Two (Save $5)	_____	x$25.00 =	$___________

Any Three (Save $5)	_____	x$40.00 =	$___________

Any Four (Save $10)	_____	x$50.00 =	$___________

Any Five (Save $15)	_____	x$60.00 =	$___________

All Six (Save $25)	_____	x$65.00 =	$___________

Wisconsin Residents add State/County Sales Tax	$___________

Total Enclosed US$	$___________

I got the Shareware versions from :_______________________________________________________

!!! Thanks for your Support !!!

P. Scott Antony/Spreadsheet Solutions

Copyright 1995, by Spreadsheet Solutions